﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Net;
using System.IO;

namespace Exporter
{
    class Program
    {
        private static SalesManagerEntities _sme = new SalesManagerEntities();
        private static DateTime startDate = DateTime.MinValue;
        private static DateTime endDate = DateTime.MinValue;
        private static Int32 OwnerID = -1;
        private static string OwnerName = string.Empty;
        private static Int32 _recordCount = 0;
        private static Int32 _fileIndex = 0;

        static void Main(string[] args)
        {
            List<Int32> exportIDs = new List<Int32>();

            bool usage = false;
            foreach (string arg in args)
            {
                // Parse out the command line.
                string name = arg.Split(new char[] {'='})[0], 
                    value = arg.Split(new char[] {'='})[1];
                if (name.ToLower() == "exportid")
                {
                    Int32 iValue;

                    if (Int32.TryParse(value, out iValue))
                        exportIDs.Add(iValue);
                    else
                        usage = true;
                }
                else if (name.ToLower() == "schedulename")
                {
                    foreach (tblExportManager em in from c in _sme.tblExportManager where c.ScheduleName == value select c)
                        exportIDs.Add(em.ExportID);
                }
                else if (name.ToLower() == "startdate")
                {
                    usage |= !DateTime.TryParse(value, out startDate);
                }
                else if (name.ToLower() == "enddate")
                {
                    usage |= !DateTime.TryParse(value, out endDate);
                }
                else
                {
                    usage = true;
                }
            }
            if (startDate == DateTime.MinValue)
            {
                usage = true;
                Console.WriteLine("Missing StartDate value");
            }
            if (endDate == DateTime.MinValue)
            {
                usage = true;
                Console.WriteLine("Missing EndDate value");
            }
            if (usage)
            {
                Console.WriteLine("Usage: Exporter StartDate=<start date> EndDate=<end date> ExportID=<exportid>[, ExportID=<exportid>...]");
                Console.WriteLine("   or: Exporter StartDate=<start date> EndDate=<end date> ScheduleName=<schedule name>");
                return;
            }

            string filePath = System.Configuration.ConfigurationManager.AppSettings["FilePath"];
            if (filePath == null)
            {
                Console.WriteLine("Missing FilePath in .config");
                return;
            }
            if (!filePath.EndsWith("\\"))
                filePath += "\\";


            // Loop through the exports
            foreach (Int32 exportID in exportIDs)
            {
                tblExportManager em = (tblExportManager)_sme.GetObjectByKey(new System.Data.EntityKey("SalesManagerEntities.tblExportManager", "ExportID", exportID));
                OwnerName = em.Owner.ShortName;
                OwnerID = em.OwnerID;

                if (em != null)
                {
                    tblExportHistory eh = new tblExportHistory();
                    eh.ExportID = exportID;
                    eh.CreateDate = DateTime.Now;

                    try
                    {
                        string targetFile = Path.GetTempFileName();
                        string query = em.Query;

                        if (em.Format.ToLower().Trim() == "excel")
                            eh.Records = ExportXls(query, targetFile);
                        if (em.Format.ToLower().Trim() == "csv")
                            eh.Records = ExportCsv(query, targetFile);

                        // Copy the temp file to the named path
                        string fullName = filePath + ParseText(em.FileName);
                        if (em.OverwriteFile || !File.Exists(fullName))
                            File.Copy(targetFile, fullName, true);
                        else if (em.FileName.ToLower().Contains("%%index%%"))
                        {
                            for (_fileIndex++; File.Exists(filePath + ParseText(em.FileName)); _fileIndex++) ;
                            File.Copy(targetFile, fullName, true);
                        }
                        else
                            throw new Exception("Target file already exists");
                        File.Delete(targetFile);

                        if (em.MailTo != null && em.MailTo != string.Empty && (em.SendIfEmpty || eh.Records > 0))
                            SendMail(em.MailTo, em.MailCc, ParseText(em.MailSubject), ParseText(em.MailBody), (em.AttachFile) ? fullName : null);

                        if (em.FtpSite != null && em.FtpSite != string.Empty && (em.SendIfEmpty || eh.Records > 0))
                            eh.Messages = SendFtp(em.FtpSite, ParseText(em.FtpDirectory), em.FtpUser, em.FtpPwd, fullName);
                    }
                    catch (Exception err)
                    {
                        eh.Messages = err.Message;
                    }
                    _sme.AddTotblExportHistory(eh);
                    _sme.SaveChanges();
                }
            }
        }

        public static string ParseTextEvaluator(Match match)
        {
            string name = match.Groups["name"].Value;
            string modifier = string.Empty;
            if (match.Groups["modifier"] != null)
                modifier = match.Groups["modifier"].Value;

            if (name.ToLower() == "startdate")
                return startDate.ToString((modifier == null) ? "yyyy-MM-dd hh:mm" : modifier);
            if (name.ToLower() == "enddate")
                return endDate.ToString((modifier == null) ? "yyyy-MM-dd hh:mm" : modifier);
            if (name.ToLower() == "ownerid")
                return OwnerID.ToString();
            if (name.ToLower() == "ownername")
                return OwnerName;
            if (name.ToLower() == "recordcount")
                return _recordCount.ToString();
            if (name.ToLower() == "index")
                return _fileIndex.ToString();
            return string.Empty;
        }

        private static string ParseText(string txt)
        {
            string ret;

            Regex regex = new Regex(@"%%(?<name>\w+)(\s+(?<modifier>.+?))?%%");
            ret = regex.Replace(txt, ParseTextEvaluator);

            return ret;
        }

        public static string ParseSqlEvaluator(Match match)
        {
            string name = match.Groups["name"].Value;

            string following = (match.Groups["following"] == null ) ? string.Empty : match.Groups["following"].Value;
            if (name.ToLower() == "startdate")
                return "'" + startDate.ToString("yyyy-MM-dd hh:mm") + "'" + following;
            if (name.ToLower() == "startdate")
                return "'" + endDate.ToString("yyyy-MM-dd hh:mm") + "'" + following;
            if (name.ToLower() == "ownerid")
                return OwnerID.ToString() + following;
            return string.Empty;
        }

        private static string ParseSql(string txt)
        {
            string ret;

            Regex regex = new Regex(@"@(?<name>\w+)(?<following>[\W$])");
            ret = regex.Replace(txt, ParseSqlEvaluator);
            regex = new Regex(@"@(?<name>\w+)$");
            ret = regex.Replace(ret, ParseSqlEvaluator);

            return ret;
        }

        private static int ExportXls(string sql, string targetPath )
        {
            ADODB.Connection conn = new ADODB.Connection();
            ADODB.Recordset rs = new ADODB.Recordset();

            conn.ConnectionString = "Provider=SQLNCLI;" + ((System.Data.EntityClient.EntityConnection)_sme.Connection).StoreConnection.ConnectionString;
            conn.Open();
            conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
            conn.CommandTimeout = 1200;
            object recordsAffected;
            rs = conn.Execute(ParseSql(sql), out recordsAffected);

            Microsoft.Office.Interop.Excel.Application exl = new Microsoft.Office.Interop.Excel.Application();
            exl.DisplayAlerts = false;
            Microsoft.Office.Interop.Excel.Workbook wb = exl.Workbooks.Add();
            Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
            ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[2]).Delete();
            ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[2]).Delete();

            ws.Range["A2"].CopyFromRecordset(rs);
            int recordCount = rs.RecordCount;

            Microsoft.Office.Interop.Excel.Range r;
            for( int colIndex = 0; colIndex < rs.Fields.Count; colIndex++)
            {
                ws.Cells[1, colIndex+1] = rs.Fields[colIndex].Name;
                r = (Microsoft.Office.Interop.Excel.Range)ws.Cells[1, colIndex+1];
                r.Interior.ColorIndex = 36;
                r.Font.Bold = true;
                r.HorizontalAlignment = 3;
            }

            char lastCol = System.Convert.ToChar(Math.Min(65 + rs.Fields.Count - 1, 90));
            r = (Microsoft.Office.Interop.Excel.Range)ws.Columns["A:" + lastCol];
            r.WrapText = false;
            r.AutoFit();
            conn.Close();

            //'Make Excel available to the user. 
            exl.ActiveWorkbook.SaveAs(targetPath, 1);
            exl.ActiveWorkbook.Close();

            wb = null;
            ws = null;
            rs = null;
            exl.Application.Quit();
            exl = null;

            return recordCount;
        }

        private static int ExportCsv(string sql, string targetPath)
        {
            SqlConnection conn = (SqlConnection)((System.Data.EntityClient.EntityConnection)_sme.Connection).StoreConnection;
            if (conn.State != System.Data.ConnectionState.Open)
                conn.Open();
            SqlCommand cmm = conn.CreateCommand();

            cmm.CommandText = sql;
            cmm.CommandTimeout = 1200;
            cmm.Parameters.Add(new SqlParameter("@StartDate", startDate));
            cmm.Parameters.Add(new SqlParameter("@EndDate", endDate));
            cmm.Parameters.Add(new SqlParameter("@OwnerID", OwnerID));

            SqlDataReader rdr = cmm.ExecuteReader();
            StreamWriter sw = new StreamWriter(targetPath, false);

            for( int i=0; i < rdr.FieldCount; i++)
                if (!rdr.GetName(i).StartsWith("&&"))
                {
                    if (i > 0)
                        sw.Write(",");
                    sw.Write("\"" + rdr.GetName(i) + "\"");
                }
            sw.WriteLine();

            int recordCount = 0;
            while (rdr.Read())
            {
                recordCount++;
                object[] values = new object[rdr.FieldCount];
                for (int i = 0; i < rdr.GetValues(values); i++)
                {
                    if (!rdr.GetName(i).StartsWith("&&"))
                    {
                        if (i > 0)
                            sw.Write(",");
                        if (values[i] is DateTime)
                            sw.Write(((DateTime)values[i]).ToString("MM/dd/yyyy"));
                        else if (values[i] is string)
                            sw.Write("\"" + ((string)values[i]).Replace("\"", "\"\"").Trim() + "\"");
                        else
                            sw.Write(values[i].ToString());
                    }
                }
                sw.WriteLine();
            }

            sw.Close();
            return recordCount;
        }


        private static string SendFtp(string site, string directory, string UID, string PWD, string targetPath)
        {
            FtpWebRequest req = (FtpWebRequest)FtpWebRequest.Create("ftp://" + site + "/" + targetPath);
            req.Credentials = new NetworkCredential(UID, PWD);
            req.Method = WebRequestMethods.Ftp.UploadFile;
            req.UseBinary = true;
            Stream reqStream = req.GetRequestStream();

            Byte[] bFile = File.ReadAllBytes(targetPath);

            reqStream.Write(bFile, 0, bFile.Length);
            reqStream.Close();
            reqStream.Dispose();

            FtpWebResponse resp = (FtpWebResponse)req.GetResponse();
            return resp.StatusDescription;
        }

        private static void SendMail( string mailTo, string mailCc, string subject, string body, string targetPath)
        {
            string mailServer = System.Configuration.ConfigurationManager.AppSettings["MailServer"];
            if (mailServer == null)
                throw new Exception("MailServer is not set in the confirutaion");
            System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient(mailServer);
            System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage();

            message.Subject = subject;
            foreach( string to in mailTo.Split(new char[] {';'}))
                if (to.Trim() != string.Empty) 
                    message.To.Add(to);

            if (mailCc != null)
                foreach( string cc in mailCc.Split(new char[] {';'}))
                    if (cc.Trim() != string.Empty) 
                        message.CC.Add(cc);
            message.Body = body;
            message.From = new System.Net.Mail.MailAddress(System.Configuration.ConfigurationManager.AppSettings["MailFrom"]);
            if (targetPath != null)
                message.Attachments.Add(new System.Net.Mail.Attachment(targetPath));

            if (message.To.Count > 0)
            {
                client.Credentials = new NetworkCredential(System.Configuration.ConfigurationManager.AppSettings["MailUID"], 
                    System.Configuration.ConfigurationManager.AppSettings["MailPWD"]);
                client.Send(message);
            }
        }
    }
}
